package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.common.OrderCode;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.StoreImages;
import cn.com.dashihui.web.dao.User;

public class StoreService {

	public boolean findExistsByTitle(String title) {
		return Store.me().findFirst("SELECT * FROM t_dict_store WHERE title=?", title) != null;
	}

	public boolean findExistsByStoreId(String storeId) {
		return Store.me().findFirst("SELECT * FROM t_dict_store WHERE storeId=?", storeId) != null;
	}
	public boolean findExistsByTel(String tel) {
		return Store.me().findFirst("SELECT * FROM t_dict_store WHERE tel=?", tel) != null;
	}
	/**
	 * 根据手机号判断前端用户是否存在
	 * @param storeId
	 * @return
	 */
	public boolean findExistsByaphone(String aphone) {
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE phone=?", aphone) != null;
	}


	/**
	 * 分页查找
	 */
	public Page<Record> findByPage(int pageNum, int pageSize, String storeid, int state, String sellerId,String title) {
		StringBuffer sBuffer = new StringBuffer(
				" FROM t_dict_store T INNER JOIN t_bus_seller C on T.sellerId=C.sellerId WHERE 1=1 AND  C.sellerId=?  ");
		List<Object> params = new ArrayList<Object>();
		params.add(sellerId);
		if (!StrKit.isBlank(storeid) && !"0".equals(storeid)) {
			sBuffer.append(" AND T.storeid=?");
			params.add(storeid);
		}
		if (!StrKit.isBlank(title)) {
			sBuffer.append(" AND T.title like ? ");
			params.add("%"+title+"%");
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND T.qstate=1 and T.state=1");
			break;
		case 2:
			sBuffer.append(" AND T.qstate=1 and T.state=2");
			break;
		case 3:
			sBuffer.append(" AND T.qstate=1 and T.state=3");
			break;
		case 4:
			sBuffer.append("AND T.qstate=1 and T.state=4");
			break;
		case 5:
			sBuffer.append(" AND T.qstate=2");
			break;
		case 6:
			sBuffer.append(" AND T.qstate=3");
			break;
		}

		sBuffer.append(" ORDER BY T.createtime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.*", sBuffer.toString(), params.toArray());
	}

	public List<Record> find(int province, int city, int area, String title) {
		StringBuffer sBuffer = new StringBuffer("select id,title FROM t_dict_store T WHERE 1=1");
		List<Object> params = new ArrayList<Object>();
		if (StrKit.notBlank(title)) {
			sBuffer.append(" AND T.title LIKE ?");
			params.add("%" + title + "%");
		}
		if (area != 0) {
			sBuffer.append(" AND T.province=? AND T.city=? AND T.area=?");
			params.add(province);
			params.add(city);
			params.add(area);
		} else if (city != 0) {
			sBuffer.append(" AND T.province=? AND T.city=?");
			params.add(province);
			params.add(city);
		} else if (province != 0) {
			sBuffer.append(" AND T.province=?");
			params.add(province);
		}
		sBuffer.append(" ORDER BY T.starttime DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}

	public boolean addStore(Store newObject) {
		return newObject.save();
	}

	public boolean delStore(int id) {
		return Store.me().deleteById(id);
	}

	public boolean editStore(Store object) {
		return object.update();
	}

	public String goodsName(int id){
		return Db.queryStr("SELECT name from t_bus_goods where id=?", id);
	}
	/**
	 * 根据电话号码获取用户对象
	 * @param phone
	 * @return
	 */
	public User getUserByPhone(String phone){
		return User.me().findFirst("SELECT name from t_bus_user where phone=?", phone);
	}
	
	/**
	 * 编辑后查找商品
	 * 
	 * @param id
	 * @return
	 */
	public Store findByIdStore(int id) {
		return Store.me().findFirst(
				"SELECT T.* FROM t_dict_store T INNER JOIN t_bus_seller C on T.sellerId=C.sellerId WHERE 1=1 AND  T.id=? ",id);
	}

	public Store findByIdStoreid(String storeid) {
		return Store.me().findFirst(
				"SELECT T.* FROM t_dict_store T INNER JOIN t_bus_seller C on T.sellerId=C.sellerId WHERE 1=1 AND  T.storeid=? ",storeid);
	}
	public Store findByStoreid(String storeId) {
		return Store.me().findFirst("select * from t_dict_store where storeid=?",storeId);
	}
	public Store findById(int id) {
		return Store.me().findFirst("SELECT * FROM t_dict_store WHERE id=?", id);
	}

	/**
	 * 从对象中获取公司sellerid
	 */
	public Record FindById(int userId) {
		return Db.findFirst("select * from t_bus_seller where userId=?", userId);
	}

	public Record findByUserId(int userid) {
		return Db.findFirst("select sellerId from t_bus_seller where userid=?", userid);
	}
	//查询所有公司
	public List<Record> findAllBus() {
		return Db.find("select T.sellerId,T.sellerName from t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id");
	}

	/**
	 * 查找添加页面中的城市
	 * 
	 * @return
	 */

	public List<Record> findCity() {
		// TODO Auto-generated method stub
		// return Db.find("select * from t_dict_store");
		return Db.find("select id,name from t_dict_city where type=2");
	}

	/**
	 * 查找编辑页面中的城市
	 * 
	 * @return
	 */

	public Record EditCity(String storeid) {
		// TODO Auto-generated method stub
		// return Db.find("select * from t_dict_store");
		return Db.findFirst(
				"select id,name from t_dict_city where type=2 and id=select city from t_dict_store where storeid=?",
				storeid);
	}

	public List<Record> findAllSeller() {
		// TODO Auto-generated method stub
		// return Db.find("select * from t_dict_store");
		return Db.find("select state,title,storeid from t_dict_store ");
	}
	/**
	 * 查询所有分公司
	 */
	public List<Record> findAllStore() {
		return Db.find("select T.storeid,T.title,T.sellerId from t_dict_store T inner JOIN t_auth_admin_seller A ON T.storeid = A.storeid");
	}
	/**
	 * 查询公司的状态是否通过
	 * 
	 * @return
	 */
	public Record findState(int userid) {
		// TODO Auto-generated method stub
		/*
		 * if(RoleId==1||RoleId==2){ return Db.findFirst(
		 * "SELECT  * from  t_bus_seller where userid=? ",userid); }else{ return
		 * Db.findFirst(
		 * "SELECT  * from  t_dict_store where storeid=(select storeid from t_auth_admin_seller where id=?)"
		 * ,userid); }
		 */
		return Db.findFirst("SELECT  * from  t_bus_seller where userid=? ", userid);
	}

	/**
	 * 查询分公司的开店状态是否正常
	 * 
	 * @return
	 */
	public Record findQstate(int userid) {
		return Db.findFirst("SELECT  * from  t_dict_store where userid=? ", userid);
	}

	/**
	 * 根据公司userid查询分公司
	 */
	public List<Record> findAllStoreid(int userid) {
		return Db.find( "select storeid,title from t_dict_store where sellerId=(select sellerId from t_bus_seller where userid=? )",userid);
	}

	/**
	 * 查询分公司的storeid
	 * 
	 * @return
	 */
	public Record findStoreState(int userid) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT  A.storeid from  t_dict_store as A inner JOIN t_auth_admin_seller as B ON A.storeid=B.storeid where B.id=?",userid);
	}
	/**
	 * 分公司Id查所属公司
	 * @param storeid
	 * @return
	 */
	public Record findBysellerId(String storeid){
		return Db.findFirst("select sellerId from t_dict_store where storeid = ?",storeid);
	}
	
	/**
	 * 根据userid,获取角色
	 * 
	 * @param userid
	 * @return
	 */
	public int findByRoleId(int userid) {
		return Db.queryInt("SELECT C.id  from t_auth_admin_seller as A inner JOIN  t_auth_admin_roles_seller as B ON A.id=B.userid  INNER JOIN t_auth_roles_seller AS C ON B.roleid = C.id where userid=?",userid);
	}
	/*
	 * public List<Record> chooseStore(String sSellerId) { return Db.find(
	 * "select storeid,title,address,tel from t_dict_store where sellerId=?"
	 * ,sSellerId); }
	 */

	public List<Record> chooseStore(String sellerId) {
		return Db.find("select * from t_dict_store where sellerId=?",sellerId);
	}

	public List<Record> chooseStore(int userid) {
		return Db.find("select B.* from t_bus_seller as A INNER JOIN t_dict_store as B on A.sellerId=B.sellerId  where 1=1 and A.userid=?",userid);
	}

	public int doCancel(String storeid, String checktime) {
		return Db.update("update t_dict_store set state=4 ,checktime=? where storeid=?", checktime, storeid);
	}

	public int doAccept(String storeid, String checktime) {
		return Db.update("update t_dict_store set state=2 ,checktime=? where storeid=?", checktime, storeid);
	}

	public int doCancel2(String storeid, String checktime) {
		return Db.update("update t_dict_store set state=2 ,checktime=? where storeid=?", checktime, storeid);
	}

	// 上线Online
	public int Online(String storeid, String starttime) {
		return Db.update("update t_dict_store set state=6 , qstate=1 , starttime=? where storeid=?", starttime,storeid);
	}

	// 下线方法
	public int Offline(String storeid, String dowetime) {
		return Db.update("update t_dict_store set state=3, qstate=1,downtime=? where storeid=?", dowetime, storeid);
	}

	public int doAccept2(String storeid, String checktime) {
		return Db.update("update t_dict_store set state=4 ,checktime=? where storeid=?", checktime, storeid);
	}

	public Record detail(String id) {
		return Db.findFirst("select S.*,C.name" + " " + "from t_dict_store S , t_dict_city C where S.city=C.id and S.id=?", id);
	}

	public Store findStroe(int id) {
		return Store.me().findById(id);
	}

	/**
	 * 分公司图片列表
	 * 
	 * @param storeid
	 * @return
	 */
	public List<StoreImages> findAllImages(String stroeimagenum) {
		return StoreImages.me().find("SELECT A.* FROM t_bus_store_images A WHERE A.stroeimagenum=? ORDER BY A.orderNo ",stroeimagenum);
	}

	/**
	 * 图片排序
	 * 
	 * @param sortMap
	 * @return
	 */
	public boolean sortImages(Map<String, String> sortMap) {
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for (String id : sortMap.keySet()) {
			sqlList.add("UPDATE t_bus_store_images SET orderNo=" + sortMap.get(id) + " WHERE id=" + id);
		}
		int[] result = Db.batch(sqlList, batchSize);
		return result.length > 0;
	}

	/**
	 * 添加图片
	 * 
	 * @param newObject
	 * @return
	 */
	public boolean addImage(StoreImages newObject) {
		return newObject.save();
	}

	/**
	 * 删除图片
	 * 
	 * @param id
	 * @return
	 */
	public boolean delImage(int id) {
		return StoreImages.me().deleteById(id);
	}

	/**
	 * 导出数据
	 * 
	 * @param response
	 * @param request
	 * @param listz
	 * @param titles2
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles,
			List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "分公司管理列表", titles, listz);
	}

	public List<Record> findExcelByPage(int state, int qstate, String storeid, String sellerId) {
		StringBuffer sBuffer = new StringBuffer(
				"SELECT T.storeid,T.title,T.scope,T.createtime,T.aname,T.aphone,T.address,T.state,T.starttime,T.downtime, T.qstate FROM t_dict_store T INNER JOIN t_bus_seller C on T.sellerId=C.sellerId WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" AND C.sellerId=?");
		params.add(sellerId);
		if (!StrKit.isBlank(storeid) && !"0".equals(storeid)) {
			sBuffer.append(" AND T.storeid=?");
			params.add(storeid);
		}
		if (state <= 3 && state != 0) {
			sBuffer.append(" AND T.state=?");
			params.add(state);
		} else if (state - 3 > 0) {
			qstate = state - 3;
			sBuffer.append(" AND T.qstate=?");
			params.add(qstate);
		}
		sBuffer.append(" ORDER BY T.createtime DESC");
		return Db.find(sBuffer.toString(), params.toArray());

	}

	/**
	 * 导出数据
	 * 
	 * @param response
	 * @param request
	 * @param listz
	 * @param titles2
	 * @param member
	 */
	public void jyexport(HttpServletResponse response, HttpServletRequest request, List<Pair> titles,
			List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "交易详情列表", titles, listz);
	}
/**
 * 商品交易导出
 * @param id
 * @param state
 * @param sellerId
 * @param storeid
 * @return
 */
	public List<Record> jyfindExcelByPage(int id, int state, String sellerId, String storeid) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
	
		sBuffer.append("SELECT A.*,C.title as xdstore,T.name ,H.title as dhstore FROM t_bus_order AS A INNER JOIN t_dict_store AS C ON A.storeid = C.storeid "
				+ "INNER JOIN t_bus_goods AS F ON  F.id=A.goodsid "
				+ "left JOIN t_bus_seller D on C.sellerId=D.sellerId "
				+ "left JOIN t_bus_user_wx K on A.userid=K.id "
				+ "left join t_bus_user T on K.userid=T.id "
				+ "left JOIN t_dict_store H ON A.dhstoreid = H.storeid where 1=1");
		
		if (id > 0) {
			sBuffer.append(" AND F.id=?");
			params.add(id);
		}

		if (!StrKit.isBlank(sellerId)) {
			sBuffer.append(" AND D.sellerId=? ");
			params.add(sellerId);
		}
		if (!StrKit.isBlank(storeid)) {
			sBuffer.append(" AND A.storeid=?");
			params.add(storeid);
		}
		switch (state) {
		case 1:// 未付款（
			sBuffer.append(" AND payState=? AND delState=?");
			params.add(OrderCode.OrderPayState.NO_PAY);
			params.add(OrderCode.DelState.NO);
			break;
		case 2:// 未兑换
			sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.NO_CON);
			params.add(OrderCode.RefundState.ZERO);
			break;
		case 3:// 已付款
			sBuffer.append(" AND payState=? AND orderState=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);

			params.add(OrderCode.OrderState.had_CON);
			break;
		case 4:// 过期
			sBuffer.append(" AND payState=? AND orderState=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.EXPIRE);
			break;
		case 5:// 删除
			sBuffer.append(" AND payState=? AND delState=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.DelState.YES);
			break;
		case 6:// 取消
			sBuffer.append(" AND payState=? AND delState=?");
			params.add(OrderCode.OrderPayState.NO_PAY);
			params.add(OrderCode.DelState.YES);
			break;
		case 7:// 退款中
			sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.NO_CON);
			params.add(OrderCode.RefundState.VERIFYING);
			break;
		case 8:// 已退款
			sBuffer.append(" AND payState=? AND orderState=? and tuistate=?");
			params.add(OrderCode.OrderPayState.HAD_PAY);
			params.add(OrderCode.OrderState.NO_CON);
			params.add(OrderCode.RefundState.REFUNDED);
			break;
		/*
		 * default: //默认不查询取消、过期、删除的订单 sBuffer.append(
		 * " AND orderState!=? AND orderState!=? AND orderState!=?");
		 * params.add(OrderCode.OrderState.EXPIRE); break;
		 */
		}
		sBuffer.append(" ORDER BY A.createDate DESC");
		return Db.find(sBuffer.toString(), params.toArray());
	}

	/**
	 * 查询兑换码的状态
	 */
	public List<Record> getOrderByOrder(String duihuanma) {
		String sql = "SELECT * FROM t_bus_order WHERE duihuanma=?";
		return Db.find(sql, duihuanma);
	}
	//根据公司查询所有分公司
	public  List<Record> chooseAllStore(String sBusId) {
		return Db.find("select storeid,title from t_dict_store where sellerId in ('"+sBusId+"')");
	}
	/**
	 * 根据分公司id查询当前所属公司
	 * @param phone
	 * @return
	 */
	public Store findSellerByStoreid(String storeid){
		return Store.me().findFirst("SELECT sellerId from t_dict_store where storeid=?", storeid);
	}

}
